using System;
using System.Collections.Generic;
using System.Text;
using DTO;
using System.Data;
using System.Data.SqlClient;

namespace DAO
{
	/// <summary> 
	///Author: nnamthach@gmail.com 
	/// <summary>
    public class ItemRequestDataAccess : Connection
    {
		#region Local Variable
        private string _strSPInsertName = "dbo.[procItemRequest_add]";
        private string _strSPUpdateName = "dbo.[procItemRequest_update]";
        private string _strSPDeleteName = "dbo.[procItemRequest_delete]";
        private string _strSPGetName = "dbo.[procItemRequest_get]";
        private string _strSPGetAllName = "dbo.[procItemRequest_getall]";
		private string _strSPGetPages = "dbo.[procItemRequest_getpaged]";
		private string _strSPIsExist = "dbo.[procItemRequest_isexist]";
        private string _strTableName = "[ItemRequest]";
		private string _strSPGetTransferOutName = "dbo.[procItemRequest_gettransferout]";
		string _strSPGetCountName = "procItemRequest_getcount";
        string _strSPGetByIndexName = "procItemRequest_getindex";
		#endregion Local Variable
		
		#region Method
        public ItemRequestInfo Get(
        String DTB,
        String RequestRef,
		ref string sErr)
        {
			ItemRequestInfo objEntr = new ItemRequestInfo();
			InitConnect();
			InitSPCommand(_strSPGetName);              
            AddParameter(ItemRequestInfo.Field.DTB.ToString(), DTB);
            AddParameter(ItemRequestInfo.Field.RequestRef.ToString(), RequestRef);
            
            DataTable list = new DataTable();
            try
            {
                list = executeSelect();
            }
            catch (Exception ex)
            {
                sErr = ex.Message;
            }           
            
            if (list.Rows.Count > 0)
                objEntr = new ItemRequestInfo(list.Rows[0]);
            //if (dr != null) list = CBO.FillCollection(dr, ref list);
            if (sErr != "") ErrorLog.SetLog(sErr);
            return objEntr;
        }      

        public DataTable GetAll(
        String DTB,
        ref string sErr)
        {
            InitConnect();
            InitSPCommand(_strSPGetAllName);
			AddParameter(ItemRequestInfo.Field.DTB.ToString(), DTB);
            DataTable list = new DataTable();
            try
            {
                list = executeSelect();
            }
            catch (Exception ex)
            {
                sErr = ex.Message;
            }      


            if (sErr != "") ErrorLog.SetLog(sErr);
            return list;
        }
		public DataTable GetByPos(
        String DTB,
        int pos, ref string sErr)
        {
            InitConnect();
            InitSPCommand(_strSPGetByIndexName);
			AddParameter("INX", pos);
			AddParameter(ItemRequestInfo.Field.DTB.ToString(), DTB);
            DataTable list = new DataTable();
            try
            {
                list = executeSelect();
            }
            catch (Exception ex)
            {
                sErr = ex.Message;
            }          


            if (sErr != "") ErrorLog.SetLog(sErr);
            return list;
        }
		public int GetCountRecord(
        String DTB,
        ref string sErr)
        {
			int ret = -1;
            openConnection();
            InitSPCommand(_strSPGetCountName);
            AddParameter(ItemRequestInfo.Field.DTB.ToString(), DTB);
          
            try
            {
                //command.ExecuteNonQuery();
                object tmp = executeScalar();
                if(tmp != null && tmp != DBNull.Value)
					ret = Convert.ToInt32(tmp);
				else 
					ret=0;
            }
            catch (Exception ex)
            {
                sErr = ex.Message;
            }
            finally {closeConnection();}
            if (sErr != "") ErrorLog.SetLog(sErr);
			
            return ret;			
        }
		/// <summary>
        /// Return 1: Table is exist Identity Field
        /// Return 0: Table is not exist Identity Field
        /// Return -1: Erro
        /// </summary>
        /// <param name="tableName"></param>
        public Int32 Add(ItemRequestInfo objEntr, ref string sErr)
        {
            int ret = -1;
            openConnection();
            InitSPCommand(_strSPInsertName);
            AddParameter(ItemRequestInfo.Field.DTB.ToString(), objEntr.DTB);
            AddParameter(ItemRequestInfo.Field.RequestRef.ToString(), objEntr.RequestRef);
            AddParameter(ItemRequestInfo.Field.RequestDate.ToString(), objEntr.RequestDate);
            AddParameter(ItemRequestInfo.Field.PreparedBy.ToString(), objEntr.PreparedBy);
            AddParameter(ItemRequestInfo.Field.ApprovedBy.ToString(), objEntr.ApprovedBy);
            AddParameter(ItemRequestInfo.Field.CreatedBy.ToString(), objEntr.CreatedBy);
            AddParameter(ItemRequestInfo.Field.CreatedDate.ToString(), objEntr.CreatedDate);
            AddParameter(ItemRequestInfo.Field.ModifiedBy.ToString(), objEntr.ModifiedBy);
            AddParameter(ItemRequestInfo.Field.ModifiedDate.ToString(), objEntr.ModifiedDate);
          
            try
            {
                //command.ExecuteNonQuery();
                object tmp = executeScalar();
                if(tmp != null && tmp != DBNull.Value)
					ret = Convert.ToInt32(tmp);
				else 
					ret=0;
            }
            catch (Exception ex)
            {
                sErr = ex.Message;
            }
            finally {closeConnection();}
            if (sErr != "") ErrorLog.SetLog(sErr);
			
            return ret;
        }

        public string Update(ItemRequestInfo objEntr)
        {
            openConnection();
            InitSPCommand(_strSPUpdateName);
            AddParameter(ItemRequestInfo.Field.DTB.ToString(), objEntr.DTB);
            AddParameter(ItemRequestInfo.Field.RequestRef.ToString(), objEntr.RequestRef);
            AddParameter(ItemRequestInfo.Field.RequestDate.ToString(), objEntr.RequestDate);
            AddParameter(ItemRequestInfo.Field.PreparedBy.ToString(), objEntr.PreparedBy);
            AddParameter(ItemRequestInfo.Field.ApprovedBy.ToString(), objEntr.ApprovedBy);
            AddParameter(ItemRequestInfo.Field.CreatedBy.ToString(), objEntr.CreatedBy);
            AddParameter(ItemRequestInfo.Field.CreatedDate.ToString(), objEntr.CreatedDate);
            AddParameter(ItemRequestInfo.Field.ModifiedBy.ToString(), objEntr.ModifiedBy);
            AddParameter(ItemRequestInfo.Field.ModifiedDate.ToString(), objEntr.ModifiedDate);
               
            string sErr = "";
            try
            {
                executeNonQuery();
            }
            catch (Exception ex)
            {
                sErr = ex.Message;
            }
            finally {closeConnection();}
            if (sErr != "") ErrorLog.SetLog(sErr);
            return sErr;
        }

        public string Delete(
        String DTB,
        String RequestRef
		)
        {
            openConnection();
            InitSPCommand(_strSPDeleteName);
            AddParameter(ItemRequestInfo.Field.DTB.ToString(), DTB);
            AddParameter(ItemRequestInfo.Field.RequestRef.ToString(), RequestRef);
              
            string sErr = "";
            try
            {
                executeNonQuery();
            }
            catch (Exception ex)
            {
                sErr = ex.Message;
            }
            finally {closeConnection();}
            if (sErr != "") ErrorLog.SetLog(sErr);
            return sErr;
        }  
		
		
        public Boolean IsExist(
        String DTB,
        String RequestRef
		)
        {
            InitConnect();
            InitSPCommand(_strSPIsExist);
            AddParameter(ItemRequestInfo.Field.DTB.ToString(), DTB);
            AddParameter(ItemRequestInfo.Field.RequestRef.ToString(), RequestRef);
              
            string sErr = "";
            DataTable list = new DataTable();
            try
            {
                list = executeSelect();
            }
            catch (Exception ex)
            {
                sErr = ex.Message;
            }
            finally {closeConnection();}
            if (sErr != "") ErrorLog.SetLog(sErr);
            if(list.Rows.Count==1)
				return true;
            return false;
        }
		
		private string CreateWhereClause(ItemRequestInfo obj)
        {
            String result = "";

            return result;
        }
        
        public DataTable Search(string columnName, string columnValue, string condition, string tableName, ref string sErr)
        {
            string query = "select * from " + _strTableName + " where " + columnName + " " + condition + " " + columnValue;
            DataTable list = new DataTable();
            InitConnect();
            try
            {
                list = executeSelectQuery(query);
            }
            catch (Exception ex)
            {
                sErr = ex.Message;
            }
           
            //if (dr != null) list = CBO.FillCollection(dr, ref list);
            //    if (sErr != "") ErrorLog.SetLog(sErr);
            return list;
        }
		public DataTable GetTransferOut(string dtb, object from, object to, ref string sErr)
        {
            InitConnect();
            InitSPCommand(_strSPGetTransferOutName);
			AddParameter("DB", dtb);
			AddParameter("FROM", from);
			AddParameter("TO", to);
            DataTable list = new DataTable();
            try
            {
                list = executeSelect();
            }
            catch (Exception ex)
            {
                sErr = ex.Message;
            }         


            if (sErr != "") ErrorLog.SetLog(sErr);
            return list;
        }
		#endregion Method


        public DataTable GetbyFilter(string db, string strFilter, ref string sErr)
        {
            InitConnect();
            string sql = @"SELECT [DTB],[RequestRef],[RequestDate],[PreparedBy],[ApprovedBy],[CreatedBy],[CreatedDate] ,[ModifiedBy],[ModifiedDate]
                        FROM [dbo].[ItemRequest]
                WHERE     (DTB = @DTB) ";
            if (strFilter != "") sql += " AND " + strFilter;
            InitCommand(sql, CommandType.Text);
            AddParameter("DTB", db);

            DataTable list = new DataTable();
            try
            {
                list = executeSelect();
            }
            catch (Exception ex)
            {
                sErr = ex.Message;
            }


            if (sErr != "") ErrorLog.SetLog(sErr);
            return list;
        }

        public DataTable GetAll(
        String DTB, string reqNo,
        ref string sErr)
        {
            InitConnect();
            InitCommand(@"SELECT [DTB],[RequestRef],[RequestDate],[PreparedBy],[ApprovedBy],[CreatedBy],[CreatedDate] ,[ModifiedBy],[ModifiedDate]
                FROM [ItemRequest]
                WHERE     (DTB = @DTB) AND (RequestRef = @RequestNo) ", CommandType.Text);
            AddParameter(ItemRequestInfo.Field.DTB.ToString(), DTB);
            AddParameter(ItemRequestInfo.Field.RequestRef.ToString(), reqNo);
            DataTable list = new DataTable();
            try
            {
                list = executeSelect();
            }
            catch (Exception ex)
            {
                sErr = ex.Message;
            }


            if (sErr != "") ErrorLog.SetLog(sErr);
            return list;
        }
    }
}
